-- MedAssist Clinic Management System
-- Stored procedures and functions from supplied database
USE `medassets`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddPatient` (
    IN `p_FirstName` VARCHAR(50),
    IN `p_LastName` VARCHAR(50),
    IN `p_DOB` DATE,
    IN `p_Gender` VARCHAR(20),
    IN `p_Phone` VARCHAR(20),
    IN `p_Email` VARCHAR(100),
    IN `p_Address` VARCHAR(255),
    IN `p_MedicalHistory` TEXT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;

INSERT INTO patient (
    FirstName, LastName, Gender, DOB, Phone, Email, Address, MedicalHistory
)
VALUES (
    p_FirstName, p_LastName, p_Gender, p_DOB, p_Phone, p_Email, p_Address, p_MedicalHistory
);

COMMIT;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddPatientWithAppointmentAndTreatment` ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;

START TRANSACTION;

INSERT INTO Patient
(FirstName, LastName, Gender, DOB, Phone, Email, Address, MedicalHistory)
VALUES
('NIGIST', 'ESHETU', 'Female', '1990-05-15', '07900123456',
'nigist.eshetu@email.com', 'London, UK',
'No known medical conditions');

SET @NewPatientID = LAST_INSERT_ID();

INSERT INTO Appointment
(PatientID, DoctorID, AppointmentDate, AppointmentTime, Reason, Status)
VALUES
(@NewPatientID, 1, '2026-08-15', '10:00:00',
'General consultation', 'Scheduled');

SET @NewAppointmentID = LAST_INSERT_ID();

INSERT INTO Treatment
(AppointmentID, Diagnosis, Prescription, TreatmentNotes, TreatmentDate)
VALUES
(@NewAppointmentID, 'Initial consultation', 'To be determined',
'Initial patient assessment', '2026-08-15');

COMMIT;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePatient` (IN `p_PatientID` INT)
BEGIN
DECLARE v_PatientCount INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_PatientCount
FROM Patient
WHERE PatientID = p_PatientID;

IF v_PatientCount = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Patient does not exist';
END IF;

DELETE FROM Patient
WHERE PatientID = p_PatientID;

COMMIT;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPatient` (IN `p_PatientID` INT)
BEGIN
SELECT
PatientID,
FirstName,
LastName,
Gender,
DOB,
Phone,
Email,
Address,
MedicalHistory
FROM patient
WHERE PatientID = p_PatientID;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePatient` (
IN `p_PatientID` INT,
IN `p_Phone` VARCHAR(20),
IN `p_Email` VARCHAR(100),
IN `p_Address` VARCHAR(255)
)
BEGIN
DECLARE v_PatientCount INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_PatientCount
FROM Patient
WHERE PatientID = p_PatientID;

IF v_PatientCount = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Patient does not exist';
END IF;

UPDATE Patient
SET Phone = p_Phone,
    Email = p_Email,
    Address = p_Address
WHERE PatientID = p_PatientID;

COMMIT;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `CalculatePatientAge` (`p_DOB` DATE)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN TIMESTAMPDIFF(YEAR, p_DOB, CURDATE());
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `GetPatientAppointmentCount` (`p_PatientID` INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE AppointmentCount INT;

SELECT COUNT(*)
INTO AppointmentCount
FROM appointment
WHERE PatientID = p_PatientID;

RETURN AppointmentCount;
END$$

DELIMITER ;
